package com.beyondsoft.mcms.common;

import java.util.Collection;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelExportUtils {

	public static Row createRow(Sheet sheet, int rownum) {
		return sheet.createRow(rownum);
	}

	public static Row createRow(Sheet sheet, int rownum, HSSFCellStyle style) {
		Row row = createRow(sheet, rownum);
		row.setRowStyle(style);
		return row;
	}

	public static Cell createCell(Row row, int column, Object value) {
		Cell cell = row.createCell(column);
		if (value instanceof Date) {
			cell.setCellValue((Date) value);
		} else {
			cell.setCellValue(value.toString());
		}
		return cell;
	}

	public static Cell createCell(Row row, int column, Object value, CellStyle style) {
		Cell cell = createCell(row, column, value);
		cell.setCellStyle(style);
		return cell;
	}

	public static Cell createCell(Row row, int column, ExcelDataElement<?> element) {
		CellStyle style = createStyle(row.getSheet().getWorkbook(), element.isBold(), null, false,
				element.getFontSize(), element.getPattern());
		return createCell(row, column, element.getData(), style);
	}

	public static int addMergedRegion(Sheet sheet, CellRangeAddress region) {
		return sheet.addMergedRegion(region);
	}

	public static int addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	public static int addMergedRegion(Sheet sheet, Cell cell, int firstRow, int lastRow, int firstCol, int lastCol) {
		for (int i = firstRow; i <= lastRow; i++)
			for (int j = firstCol + 1; j <= lastCol; j++) {
				createCell(sheet.getRow(i), j, "", cell.getCellStyle());
			}

		return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	public static CellStyle createDefaultStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setBorderBottom((short) 1);
		style.setBorderTop((short) 1);
		style.setBorderLeft((short) 1);
		style.setBorderRight((short) 1);
		return style;
	}

	public static CellStyle createStyle(Workbook workbook, boolean bold, String fontName, boolean wrap, int size,
			String pattern) {
		Font font = workbook.createFont();
		if (bold) {
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		}
		if (null != fontName && "".equals(fontName)) {
			font.setFontName(fontName);
		}

		font.setFontHeightInPoints((short) size);
		CellStyle style = createDefaultStyle(workbook);
		if (pattern != null)
			style.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat(pattern));
		style.setFont(font);
		style.setWrapText(wrap);
		return style;
	}

	@SuppressWarnings("unchecked")
	public static Workbook createWorkbook(String title, Collection<ExcelDataElement<?>> dataset) {
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet(title);
		sheet.setDefaultColumnWidth((short) 15);
		sheet.setDefaultRowHeightInPoints(18);
		Iterator<ExcelDataElement<?>> iterator = dataset.iterator();
		int rowIndex = 0, columnIndex;

		Row row;
		Cell cell;
		ExcelDataElement<?> dateEle;
		ExcelDataElement<?> dateEle_;

		while (iterator.hasNext()) {
			row = createRow(sheet, ++rowIndex);
			dateEle = iterator.next();
			columnIndex = 0;
			if (dateEle.getData() instanceof Collection<?>) {
				Iterator<ExcelDataElement<?>> iterator_ = ((Collection<ExcelDataElement<?>>) dateEle.getData())
						.iterator();

				while (iterator_.hasNext()) {
					dateEle_ = iterator_.next();
					cell = createCell(row, columnIndex, dateEle_);
					addMergedRegion(sheet, cell, rowIndex, rowIndex, columnIndex,
							columnIndex += dateEle_.getMergedCells());
					columnIndex++;
				}
			} else {
				cell = createCell(row, columnIndex, dateEle);
				addMergedRegion(sheet, cell, rowIndex, rowIndex, columnIndex, columnIndex + dateEle.getMergedCells());
			}
		}
		return workbook;
	}
}
